package cn.likun.kit.database;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;

import cn.likun.kit.database.bean.JdbcCallParam;
import cn.likun.kit.database.bean.JdbcCallParam.PARAM_TYPE;
import cn.likun.kit.database.bean.Pagination;
import cn.likun.kit.database.dialect.DialectCommons;
import cn.likun.kit.validate.ValidateCommons;

/**
 *JDBC通用工具类
 */
public class JdbcCommons {
	
	private JdbcTemplate jdbcTemplate;
	private MetaDataCommons metaDataCommons;
	private DialectCommons dialectCommons;
	
	public JdbcCommons(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException, Exception {
		this(getDataSource(driver, url, username, password));
	}
	
	public JdbcCommons(JdbcTemplate jdbcTemplate) throws ClassNotFoundException, SQLException{
		this.jdbcTemplate = jdbcTemplate;
		this.metaDataCommons = new MetaDataCommons(jdbcTemplate.getDataSource());
		this.dialectCommons = DialectCommons.getInstance(this.metaDataCommons.getDbType());
	}
	
	public JdbcCommons(DataSource dataSource) throws ClassNotFoundException, SQLException{
		this(new JdbcTemplate(dataSource));
	}
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public MetaDataCommons getMetaDataCommons() {
		return metaDataCommons;
	}

	public DialectCommons getDialectCommons() {
		return dialectCommons;
	}
	/**
	 * 计数查询
	 * @param sql 
	 * @param args sql 绑定参数,可以为空
	 * @return
	 */
	public int queryForCount(String sql ,List<Object> args) {
		if (ValidateCommons.isEmpty(args)) {
			args = new ArrayList<Object>();
		}
		Object[] argArray = args.toArray();
		String countSql = dialectCommons.buildCountSql(sql);
		Integer rowTotal = jdbcTemplate.queryForObject(countSql, argArray, Integer.class);
		return rowTotal;
	}
	/**
	 * 分页查询
	 * @param sql 
	 * @param args sql 绑定参数 , 可以为空
	 * @param pageIndex 页数
	 * @param pageLimit 每页指定条数
	 * @return
	 */
	public Pagination<Map<String,Object>> queryForPagination(String sql, List<Object> args, int pageIndex, int pageLimit) {
		if (ValidateCommons.isEmpty(args)) {
			args = new ArrayList<Object>();
		}
		int rowTotal = queryForCount(sql, args);
		
		String pagingSql = dialectCommons.buildPagingSql(sql, pageIndex, pageLimit);
		Object[] argArray = args.toArray();
		List<Map<String,Object>> results = jdbcTemplate.queryForList(pagingSql, argArray);
		return new Pagination<Map<String,Object>>(pageIndex, pageLimit, rowTotal, results);
	}
	/**
	 * 分页查询
	 * @param sql
	 * @param args sql绑定参数,可以为空
	 * @param resultType 结果类型
	 * @param pageIndex 页数
	 * @param pageLimit 每页数据条数
	 * @return
	 */
	public <T> Pagination<T> queryForPagination(String sql,List<Object> args ,Class<T> resultType , int pageIndex, int pageLimit) {
		if (ValidateCommons.isEmpty(args)) {
			args = new ArrayList<Object>();
		}
		int rowTotal = queryForCount(sql, args);
		String pagingSql = dialectCommons.buildPagingSql(sql, pageIndex, pageLimit);
		Object[] argArray = args.toArray();
		
		List<T> results = jdbcTemplate.queryForList(pagingSql, argArray, resultType);
		return new Pagination<T>(pageIndex, pageLimit, rowTotal, results);
	}
	/**
	 * 序列值(下一个)查询
	 * @param sequenceName 序列名称
	 * @return 序列值
	 */
	public long queryForSequenceNextValue(String sequenceName) {
		String sql = dialectCommons.buildDummyTableSql(dialectCommons.buildCallSql(sequenceName, null));
		return jdbcTemplate.queryForObject(sql, Long.class);
	}
	
	/**
	 * 调用存储过程,函数等
	 * @param callName call 的对象名
	 * @param args 参数列表
	 * @return 存储过程 ,函数返回值
	 */
	@SuppressWarnings("unchecked")
	public Map<Integer,Object> call (String callName, final List<JdbcCallParam> args) {
		List<String> params = new ArrayList<String>();
		if (ValidateCommons.isNotEmpty(args)) {
			for (int i = 0; i < args.size(); i++) {
				params.add("?");
			}
		}
		String callSql = dialectCommons.buildCallSql(callName, params);
		return jdbcTemplate.execute(callSql, new CallableStatementCallback() {

			@Override
			public Map<Integer,Object> doInCallableStatement(CallableStatement callableStatement)
					throws SQLException, DataAccessException {
				List<Integer> outParamIndexs = new ArrayList<Integer>();
				for (int i = 0; i < args.size(); i++) {
					JdbcCallParam jdbcCallParam = args.get(i);
					PARAM_TYPE type = jdbcCallParam.getType();
					Object value = jdbcCallParam.getValue();
					if (JdbcCallParam.PARAM_TYPE.IN.equals(type)) {
						callableStatement.setObject(i + 1, value);
					} else if (JdbcCallParam.PARAM_TYPE.OUT.equals(type)) {
						callableStatement.registerOutParameter(i + 1, (Integer)value);
					}
					outParamIndexs.add(i);
				}
				Map<Integer,Object> results = new HashMap<Integer,Object>();
				if (callableStatement.execute()) {
					for (int i : outParamIndexs) {
						Object value = null;
						JdbcCallParam arg = args.get(i);
						int sqlTye = (Integer) arg.getValue();
						switch (sqlTye) {
							case Types.BIT: 
								value = callableStatement.getBoolean(i + 1);
								break;
							case Types.TINYINT: 
								value = callableStatement.getByte(i + 1);
								break;
							case Types.SMALLINT: 
								value = callableStatement.getInt(i + 1);
								break;
							case Types.INTEGER: 
								value = callableStatement.getInt(i + 1);
								break;
							case Types.BIGINT: 
								value = callableStatement.getLong(i + 1);
								break;
							case Types.FLOAT: 
								value = callableStatement.getFloat(i + 1);
								break;
							case Types.REAL: 
								value = callableStatement.getFloat(i + 1);
								break;
							case Types.DOUBLE: 
								value = callableStatement.getDouble(i + 1);
								break;
							case Types.NUMERIC: 
								value = callableStatement.getBigDecimal(i + 1);
								break;
							case Types.DECIMAL: 
								value = callableStatement.getBigDecimal(i + 1);
								break;
							case Types.CHAR: 
								value = callableStatement.getString(i + 1);
								break;
							case Types.VARCHAR: 
								value = callableStatement.getString(i + 1);
								break;
							case Types.LONGVARCHAR: 
								value = callableStatement.getString(i + 1);
								break;
							case Types.DATE: 
								value = callableStatement.getDate(i + 1);
								break;
							case Types.TIME: 
								value = callableStatement.getTime(i + 1);
								break;
							case Types.TIMESTAMP: 
								value = callableStatement.getTimestamp(i + 1);
								break;
							case Types.BINARY: 
								value = callableStatement.getBytes(i + 1);
								break;
							case Types.VARBINARY: 
								value = callableStatement.getBytes(i + 1);
								break;
							case Types.LONGVARBINARY: 
								value = callableStatement.getBytes(i + 1);
								break;
							case Types.OTHER: 
								value = callableStatement.getObject(i + 1);
								break;
							case Types.JAVA_OBJECT: 
								value = callableStatement.getObject(i + 1);
								break;
							case Types.ARRAY: 
								value = callableStatement.getArray(i + 1);
								break;
							case Types.BLOB: 
								value = callableStatement.getBytes(i + 1);
								break;
							case Types.CLOB: 
								value = callableStatement.getString(i + 1);
								break;
							case Types.REF: 
								value = callableStatement.getRef(i + 1);
								break;
							case Types.BOOLEAN: 
								value = callableStatement.getBoolean(i + 1);
								break;
							case Types.NCHAR: 
								value = callableStatement.getString(i + 1);
								break;
							case Types.NVARCHAR: 
								value = callableStatement.getString(i + 1);
								break;
							case Types.LONGNVARCHAR: 
								value = callableStatement.getString(i + 1);
								break;
							case Types.NCLOB: 
								value = callableStatement.getString(i + 1);
								break;
							case Types.SQLXML: 
								value = callableStatement.getSQLXML(i + 1);
								break;
						}
						results.put(i, value);
					}
				}
				return results;
			}
		});
	}
	
	
	
	/**
	 * 获取数据源 (基于dbcp)
	 * @param driver 驱动
	 * @param url URL
	 * @param username 用户名
	 * @param password 密码
	 * @return
	 * @throws Exception
	 */
	public static DataSource getDataSource(String driver, String url, String username, String password) throws Exception{
		Properties properties = new Properties();
		properties.setProperty("driverClassName", driver);
		properties.setProperty("url", url);
		properties.setProperty("username", username);
		properties.setProperty("password", password);
		return BasicDataSourceFactory.createDataSource(properties);
	}
	
	/**
	 * 打开数据库连接
	 * @param dataSource
	 * @return
	 * @throws SQLException
	 */
	public static Connection open(DataSource dataSource) throws SQLException {
		return dataSource.getConnection();
	}
	/**
	 * 打开数据库连接
	 * @param driver 驱动
	 * @param url URL
	 * @param username 用户名
	 * @param password 密码
	 * @return 数据库连接
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection open(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {
		Properties properties = new Properties();
		properties.setProperty("user", username);
		properties.setProperty("password", password);
		properties.setProperty("remarks", "true");// 某些数据库(Oracle,Mysql)需要设置此属性才能抓取表备注信息
		
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url, properties);
		conn.setAutoCommit(true);
		return conn;
	}
	/**
	 * 关闭 ResultSet . Statement . Connection 三类对象
	 * @param obj 对象,自动判断是否符合上述类型
	 * @throws SQLException
	 */
	public static void close(Object obj) throws SQLException {
		if (ValidateCommons.isEmpty(obj)) {
			return;
		}
		if (obj instanceof ResultSet) {
			((ResultSet) obj).close();
		} else if (obj instanceof Statement) {
			((Statement) obj).close();
		} else if (obj instanceof Connection) {
			Connection conn = (Connection) obj;
			if (!conn.isClosed()) {
				conn.close();
			}
		}
	}
	/**
	 * 依次关闭 ResultSet . Statement . Connection
	 * @param conn Connection
	 * @param st Statement
	 * @param rs ResultSet
	 * @throws SQLException
	 */
	public static void close(Connection conn,Statement st, ResultSet rs) throws SQLException {
		try {
			close(conn);
		} finally {
			try {
				close(st);
			} finally {
				close(rs);
			}
		}
	}
	
	
	
	
	
}
